package cn.edu;

import cn.edu.dao.UserDao;
import cn.edu.pojo.User;
import cn.edu.pojo.vo.UserVO;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
public class MybatisPlus02DQLApplication {
    @Autowired
    private UserDao userDao;

    @Test
    void testGetAll01() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.lt("age", 20);
        List<User> users = userDao.selectList(queryWrapper);
        System.out.println(users);
    }

    @Test
    void testGetAll02() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().lt(User::getAge, 20);//添加条件
        List<User> users = userDao.selectList(queryWrapper);
        System.out.println(users);
    }

    @Test
    void testGetAll03() {
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.lt(User::getAge, 20);//添加条件
        List<User> users = userDao.selectList(queryWrapper);
        System.out.println(users);
    }

    @Test
    void testGetAll04() {
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.lt(User::getAge, 30);//添加条件  小于
        queryWrapper.gt(User::getAge, 20);//添加条件  大于
        List<User> users = userDao.selectList(queryWrapper);
        System.out.println(users);
    }

    @Test
    void testGetAll05() {
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.lt(User::getAge, 30).gt(User::getAge, 20);//添加条件  小于
        List<User> users = userDao.selectList(queryWrapper);
        System.out.println(users);
    }

    @Test
    void testGetAll06() {
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.lt(User::getAge, 20).or().gt(User::getAge, 30);//添加条件
        List<User> users = userDao.selectList(queryWrapper);
        System.out.println(users);
    }

    /**
     * 需求:查询数据库表中，根据输入年龄范围来查询符合条件的记录
     * 用户在输入值的时候，
     * 如果只输入第一个框，说明要查询大于该年龄的用户
     * 如果只输入第二个框，说明要查询小于该年龄的用户
     * 如果两个框都输入了，说明要查询年龄在两个范围之间的用户
     */
    @Test
    void testGetAll07() {
        //比如：我们模拟一下页面传递归来的查询数据
        UserVO uo = new UserVO();
        uo.setAge(20);
        uo.setAge2(30);
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        //以下的写法可以完成条件为非空的判断，但是问题很明显，如果条件多的话，每个条件都需要判断，代码量就比较大
        if (null != uo.getAge2()) {
            queryWrapper.lt(User::getAge, uo.getAge2());
        }
        if (null != uo.getAge()) {
            queryWrapper.gt(User::getAge, uo.getAge());
        }
        List<User> users = userDao.selectList(queryWrapper);
        System.out.println(users);
    }

    /**
     * public Children lt(boolean condition, R column, Object val) {
     * return (Children)this.addCondition(condition, column, SqlKeyword.LT, val);
     * }
     * <p>
     * condition为boolean类型，返回true，则添加条件，返回false则不添加条件
     */
    @Test
    void testGetAll08() {
        //比如：我们模拟一下页面传递归来的查询数据
        UserVO uo = new UserVO();
        uo.setAge(20);
        uo.setAge2(30);
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.lt(null != uo.getAge2(), User::getAge, uo.getAge2());
        queryWrapper.gt(null != uo.getAge(), User::getAge, uo.getAge());
        List<User> users = userDao.selectList(queryWrapper);
        System.out.println(users);
    }

    /*
    目前我们在查询数据的时候，什么都没有做默认就是查询表中所有字段的内容，
    我们所说的查询投影即不查询所有字段，
    只查询出指定内容的数据。
    * */
    @Test
    void testGetAll09() {
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.select(User::getId, User::getName, User::getAge);
        List<User> users = userDao.selectList(queryWrapper);

        System.out.println(users);
    }

    //聚合查询
    @Test
    void testGetAll10() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("count(*) as count");
        List<Map<String, Object>> userList = userDao.selectMaps(queryWrapper);
        System.out.println(userList);
    }

    //分组查询
    @Test
    void testGetAll11() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("count(*) as count,tel");
        queryWrapper.groupBy("tel");
        List<Map<String, Object>> userList = userDao.selectMaps(queryWrapper);
        System.out.println(userList);
    }

    /**
     * 根据用户名和密码查询用户
     */
    @Test
    void testGetAll12() {
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(User::getName,"nezha").eq(User::getPassword,"123456");
        User user = userDao.selectOne(queryWrapper);
        System.out.println(user);
    }

    /**
     * 需求:对年龄进行范围查询，使用lt()、le()、gt()、ge()、between()进行范围查询
     */
    @Test
    void testGetAll13() {
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.between(User::getAge, 20, 30);
        List<User> users = userDao.selectList(queryWrapper);
        System.out.println(users);
    }

    /**
     * 需求:查询表中name属性的值以`J`开头的用户信息,使用like进行模糊查询
     * - like():前后加百分号,如 %J%
     * - likeLeft():前面加百分号,如 %J
     * - likeRight():后面加百分号,如 J%
     */
    @Test
    void testGetAll14() {
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.likeRight(User::getName,"J");
        List<User> users = userDao.selectList(queryWrapper);
        System.out.println(users);
    }

    /**
     * 需求:查询所有数据，然后按照id降序
     */
    @Test
    void testGetAll15() {
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        /**
         * condition ：条件，返回boolean，
         当condition为true，进行排序，如果为false，则不排序
         * isAsc:是否为升序，true为升序，false为降序
         * columns：需要操作的列
         */
        queryWrapper.orderBy(true, false, User::getId);
        List<User> users = userDao.selectList(queryWrapper);
        System.out.println(users);
    }

}
